-- @owner: wan005
-- @date: 2024/10/8
-- @testpoint: pipelined函数嵌套创建测试，部分合理报错

--pre1:切换A库执行
@conn PrimaryDbAmode;

--step1:创建数据类型;expect:成功
create or replace type type_0027 as (c1 integer, c2 tinyint);
create or replace type tb_type_0027 as table of type_0027;
create or replace type tb_type_0027_1 as table of varchar(10);
create or replace type varray_type_0027 as varray(10) of integer;

--step2:嵌套创建管道函数，返回嵌套表类型;expect:成功
create or replace function func_pipelined_0027_out()
returns tb_type_0027 pipelined as $func_pipelined_0027_out$
declare
result text;
res type_0027;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
    res.c1 := 1;
    res.c2 := 2;
    pipe row(res);
    create or replace function func_pipelined_0027_in()
    returns tb_type_0027_1 pipelined as $func_pipelined_0027_in$
    begin
        pipe row('456');
    end;
    $func_pipelined_0027_in$ language plpgsql;
    select func_pipelined_0027_in() into result;
    raise info '%', result;
    drop function func_pipelined_0027_in();
return;
end;
$func_pipelined_0027_out$ language plpgsql;
/

select func_pipelined_0027_out();
select * from func_pipelined_0027_out();

--step3:嵌套创建管道函数，返回顺序数组类型;expect:成功
create or replace function func_pipelined_0027_out_1()
returns varray_type_0027 pipelined as $func_pipelined_0027_out_1$
declare
result text;
res integer;
begin
    for i in 1 .. count loop
        res := array[1, -2, 0, -0, 6, 7];
        pipe row(res[i]);
        end loop;
    create or replace function func_pipelined_0027_in_1()
    returns tb_type_0027_1 pipelined as $func_pipelined_0027_in_1$
    begin
        pipe row('456');
    end;
    $func_pipelined_0027_in_1$ language plpgsql;
    select func_pipelined_0027_in_1() into result;
    raise info '%', result;
    drop function func_pipelined_0027_in_1();
return;
end;
$func_pipelined_0027_out_1$ language plpgsql;
/

select func_pipelined_0027_out_1();
select * from func_pipelined_0027_out_1();

--step4:清理环境;expect:成功
drop type if exists varray_type_0027 cascade;
drop type if exists tb_type_0027_1 cascade;
drop type if exists tb_type_0027 cascade;
drop type if exists type_0027 cascade;
drop function if exists func_pipelined_0027_out;
drop function if exists func_pipelined_0027_out_1;
